﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_Alerts]
#-- Purpose:		Copies the Server Alerts to the Warehouse
#--	Last Update:	02/17/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_Alerts]
AS

--- Declare Local Variables
DECLARE	@sSQL varchar(MAX)

IF EXISTS (SELECT TOP 1 * FROM sys.servers WHERE name = '$(MSXServer)')
  BEGIN
	SELECT	@sSQL = 'DECLARE @srm_server_ssd_id int

--- Find this server in the active list of servers
SELECT	@srm_server_ssd_id = [ssd_id]
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
WHERE	[ssd_sqlserver_name_ro] = @@SERVERNAME
		AND [ssd_is_disabled] = 0

IF @srm_server_ssd_id IS NULL
  BEGIN
	--- This server was not found in the active list of servers, so find the most recent disabled record
	SELECT		TOP 1 @srm_server_ssd_id = [ssd_id]
	FROM		[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
	WHERE		[ssd_sqlserver_name_ro] = @@SERVERNAME
	ORDER BY	[ssd_id] DESC

	--- A disabled record was found for the server, so re-enable it
	IF @srm_server_ssd_id IS NOT NULL
		UPDATE	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
		SET		[ssd_is_disabled] = 0
		WHERE	[ssd_id] = @srm_server_ssd_id
				AND [ssd_is_disabled] = 1
  END

IF @srm_server_ssd_id IS NOT NULL
  BEGIN
	--- Copy Alert Stats
	INSERT INTO [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblStg_Alerts]
	(
		[alt_ssd_id],
		[alt_name],
		[alt_severity],
		[alt_message_id],
		[alt_enabled],
		[alt_occurrence_count],
		[alt_log_date]
	)
		SELECT		@srm_server_ssd_id [alt_ssd_id],
					[alt_name],
					[alt_severity],
					[alt_message_id],
					[alt_enabled],
					[alt_occurrence_count],
					[alt_log_date]
		FROM		[dbo].[tblMonitor_Alerts]
  END
ELSE
	RAISERROR(''This server has not been registered with the MSX Server'', 16, 1)'

	EXEC	(@sSQL)
  END
